# _*_coding=utf-8 _*_
"""
名称：mission_03_2
版本：v1.0
功能：
1.张三给李四通过网银转账 100 极客币，现有数据库中三张表：
一张为用户表，包含用户 ID 和用户名字，另一张为用户资产表，包含用户 ID 用户总资产，
第三张表为审计用表，记录了转账时间，转账 id，被转账 id，转账金额。
2.合理设计三张表的字段类型和表结构；
请实现转账 100 极客币的 SQL(可以使用 pymysql 或 sqlalchemy-orm 实现)
张三余额不足，转账过程中数据库 crash 等情况需保证数据一致性。
时间：2020-12-12
作者：长毛怪^^
"""

import time
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
from sqlalchemy import DateTime

Base = declarative_base()


class User_table(Base):
    __tablename__ = 'user_table'
    user_id = Column(Integer(), primary_key=True)
    user_name = Column(String(50))
    created_on = Column(DateTime(), default=datetime.now)


class Means_table(Base):
    __tablename__ = 'means_table'
    user_id = Column(Integer(), primary_key=True)
    user_means = Column(Integer())
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)


class Audit_table(Base):
    __tablename__ = 'audit_table'
    created_on = Column(DateTime(), default=datetime.now, primary_key=True)
    out_id = Column(Integer())
    in_id = Column(Integer())
    tansf_data = Column(Integer())


dburl = "mysql+pymysql://testuser:Testpass!123@192.168.1.168:3306/testdb?charset=utf8mb4"
engine = create_engine(dburl, encoding="utf-8")
Base.metadata.create_all(engine)

session_class = sessionmaker(bind=engine)
session = session_class()


def init_data():
    """
    张三的初始密码为：123456，初始资金为200极客币。
    李四的初始密码为：abcdef，初始资金为200极客币。
    """
    session.query(Audit_table).delete()
    session.query(User_table).delete()
    session.query(Means_table).delete()
    session.commit()

    user_demo = User_table(user_id=1, user_name='张三')
    user_demo2 = User_table(user_id=2, user_name='李四')

    user_demo3 = Means_table(user_id=1, user_means=200)
    user_demo4 = Means_table(user_id=2, user_means=200)

    session.add(user_demo)
    session.add(user_demo2)

    session.add(user_demo3)
    session.add(user_demo4)

    session.commit()


def change_data(data):
    query = session.query(Means_table.user_means).filter(Means_table.user_id == data[1])
    old_data = [result[0] for result in query]
    new_data = old_data[0] - data[2]
    query.update({Means_table.user_means: new_data})
    session.commit()

    query = session.query(Means_table.user_means).filter(Means_table.user_id == data[3])
    old_data = [result[0] for result in query]
    new_data = old_data[0] + data[2]
    query.update({Means_table.user_means: new_data})
    session.commit()

    audit_demo = Audit_table(out_id=data[1], in_id=data[3], tansf_data=data[2])
    time.sleep(1)
    session.add(audit_demo)
    session.commit()


def prove():
    i = 3
    data = ['stop']  # data[pass/stop, out_id, means, in_id]
    user_list = [result[0] for result in session.query(User_table.user_name)]
    if not user_list:
        init_data()
        user_list = [result[0] for result in session.query(User_table.user_name)]
    user_id_list = [int(result[0]) for result in session.query(User_table.user_id)]

    while True:
        mession_two_1 = input('请选择要办理转出的账户(如：张三要办理转账，请输入“张三”)：')

        if mession_two_1 not in user_list:
            print('您输入的账户不存在，请重新输入……')
            continue
        else:
            mession_two_2 = input('请输入账户密码：')
            account = [mession_two_1, mession_two_2]
            if account[0] == user_list[0]:
                if account[1] == '123456':
                    data[0] = 'pass'
                    data.append(user_id_list[user_list.index(account[0])])
                    break
                else:
                    i -= 1
                    if i != 0:
                        print(f'您输入的密码有误，请重新输入（您还有{i}次机会）……')
                        prove()
                    else:
                        print('您的机会用完了，5秒后程序退出')
                        time.sleep(5)
                        quit()
            elif account[0] == user_list[1]:
                if account[1] == 'abcdef':
                    data[0] = 'pass'
                    data.append(user_id_list[user_list.index(account[0])])
                    break
                else:
                    i -= 1
                    if i != 0:
                        print(f'您输入的密码有误，请重新输入（您还有{i}次机会）……')
                        prove()
                    else:
                        print('您的机会用完了，5秒后程序退出')
                        time.sleep(5)
                        quit()
    return data


def tansf_prepare(id_one):
    means = [result[0] for result in session.query(Means_table.user_means).filter(Means_table.user_id == id_one)]
    while True:
        tansf_means = input('请输入转账金额（请输入一个整数）：')
        if '.' in tansf_means:
            print('您输入的金额不是整数，请输入整数……')
            continue
        else:
            try:
                tansf_means = int(tansf_means)

                if tansf_means > int(means[0]):
                    print(f'您的金额不足，账户余额为{int(means[0])}，请重新输入转账金额……')
                    continue
                else:
                    break
            except ValueError:
                print('您输入的金额不是整数，请输入整数……')
                continue
    return tansf_means


def work():
    mession_one = input('请选择要办理的业务(如：要办理转账，请输入“2”)：1.初始化数据，2.转账：')
    if mession_one == '1':
        init_data()
        print('初始化数据完成')
    elif mession_one == '2':
        data = prove()
        while True:
            mession_two = input('您要给谁转账？请输入对方账户：')
            in_id = [result[0]
                     for result in session.query(User_table.user_id).filter(User_table.user_name == mession_two)]
            if not in_id:
                print('对方账户不存在，请核实后重新输入……')
                continue
            else:
                confirm = input(f'您正在给{mession_two}转账，确定请输入“yes”，重新输入对方账户请输入任意键：')
                if confirm == 'yes':
                    break
                else:
                    continue
        if data[0] == 'pass':
            means = tansf_prepare(data[1])
            while True:
                confirm = input(f'转账的金额为：{means}极客币，确认请输入“yes”，重新输入金额请输入任意键：')
                if confirm == 'yes':
                    data.append(means)
                    data.append(in_id[0])
                    print(f'系统正在从账户转移{data[2]}极客币至{mession_two}账户……')
                    change_data(data)
                    query = session.query(Means_table.user_means).filter(Means_table.user_id == data[1])
                    old_data = [result[0] for result in query]
                    print(f'转账完毕，您的账户余额为：{old_data[0]}极客币，3秒钟后系统自动退出，下次见……')
                    time.sleep(3)
                    break
                else:
                    means = tansf_prepare(data[1])
        else:
            print('操作不当，5秒后程序退出')
            time.sleep(5)
            quit()

    else:
        print('您选择的业务不存在，请重新选择。')
        work()


if __name__ == '__main__':
    work()
